# _*_ coding:utf-8 _*_
# @Time  : 2022-07-17
# @Author: zizlee

# 客户管理
import datetime

import pandas as pd
from typing import Optional, List
from fastapi import APIRouter, Body, Query, Header
from pydantic import BaseModel, datetime_parse, conint, constr, confloat
from fastapi.encoders import jsonable_encoder

from db import FAConnection
from hutool import security
from v1_all_api.all_response import AllResponse
from v1_all_api.all_utils import datetime_utils

customer_api = APIRouter()


class CustomerItem(BaseModel):
    user_id: conint(ge=1)
    investor_code: constr(min_length=1)
    investor_name: constr(min_length=1)
    open_date: datetime_parse.date
    expire: datetime_parse.date


@customer_api.post('/')  # 编辑一个客户信息
async def create_customer(ruizy_token: str = Header(...), customer: CustomerItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    customer.expire = customer.expire.strftime('%Y%m%d')
    customer.open_date = customer.open_date.strftime('%Y%m%d')
    db_conn = FAConnection()
    # 查询添加的客户是否存在
    exist_sql = "SELECT id FROM work_customer WHERE investor_code=%s AND user_id=%s;"
    exist_obj = db_conn.query(exist_sql, param=[customer.investor_code, customer.user_id],
                              fetchone=True, keep_conn=True)[0]
    if exist_obj:
        # 更新数据
        sql = "UPDATE work_customer SET user_id=%s,open_date=%s,expire=%s WHERE id=%s LIMIT 1;"
        prm = (customer.user_id, customer.open_date, customer.expire, exist_obj['id'])
    else:
        sql = """
            INSERT INTO work_customer (user_id,investor_code,investor_name,open_date,expire) 
            VALUES (%s,%s,%s,%s,%s);
        """
        prm = (customer.user_id, customer.investor_code, customer.investor_name, customer.open_date, customer.expire)

    success, _ = db_conn.insert(sql, param=prm)
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


@customer_api.get('/')  # 查询客户信息
async def query_customer(ruizy_token: str = Header(...), date: str = Query(...), user: int = Query(None)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    expire_date = datetime_utils.auth_datetime_string(date, f='%Y%m%d')
    user_id = 'NOUSER' if not user else user
    if not expire_date:
        return AllResponse.validate_error(msg='date format invalid!')
    sql = """
        SELECT c.id,c.user_id,c.investor_code,c.investor_name,c.open_date,c.expire,b.admin_name 
        FROM work_customer As c INNER JOIN ruizy_user As b ON c.user_id=b.id 
        WHERE c.expire>=%s AND IF('NOUSER'=%s,TRUE,c.user_id=%s);
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=[expire_date, user_id, user_id])
    return AllResponse.operate_successfully(data=records)


class CustomerCommission(BaseModel): # 客户佣金
    investor_code: constr(min_length=1)
    comm_date: datetime_parse.date
    comm_money: float
    interest: float
    comm_rate: confloat(ge=0, le=1)
    dept_rate: confloat(ge=0, le=1)
    tax: float
    note: str


@customer_api.post('/comm/')  # 添加一条佣金记录
async def create_customer_commission(ruizy_token: str = Header(...), comm_item: CustomerCommission = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not security.operator_is_super_admin(person):
        return AllResponse.forbidden_error()
    sql = """
        INSERT INTO work_customer_commission (investor_code,comm_date,comm_money,interest,comm_rate,dept_rate,tax,note) 
        VALUES (%s,%s,%s,%s,%s,%s,%s,%s);
    """
    print()
    comm_date = comm_item.comm_date.strftime('%Y%m%d')
    prm = (comm_item.investor_code, comm_date, comm_item.comm_money, comm_item.interest, comm_item.comm_rate,
           comm_item.dept_rate, comm_item.tax, comm_item.note)
    db_conn = FAConnection()
    success, _ = db_conn.insert(sql, param=prm)
    return AllResponse.create_successfully() if success else AllResponse.server_error()


# 查询佣金记录函数
def query_commission(start, end, user_id):
    start_date = datetime_utils.auth_datetime_string(start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(end, f="%Y%m%d")
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='date formate invalid!')
    sql = """
            SELECT a.id,a.investor_code,a.comm_date,a.comm_money,a.interest,a.comm_rate,a.dept_rate,a.tax,a.note,
                b.investor_name 
            FROM work_customer_commission As a 
            INNER JOIN work_customer As b ON a.investor_code=b.investor_code 
            WHERE a.comm_date>=%s AND a.comm_date<=%s AND IF('NOUSER'=%s,TRUE,b.user_id=%s) 
            ORDER BY a.comm_date DESC;
        """
    prm = (start_date, end_date, user_id, user_id)

    db_conn = FAConnection()
    records = db_conn.query(sql, param=prm)
    for item in records:
        item['comm'] = round(item['comm_money'] * item['comm_rate'], 2)  # 提成额
        item['total_comm'] = round(item['comm'] + item['interest'], 2)  # 总提成
        item['dept_comm'] = round(item['total_comm'] * item['dept_rate'], 2)  # 部门提成
        item['dept_tax'] = round(item['tax'] * item['dept_rate'], 2)
        item['dept_tax_comm'] = round(item['dept_comm'] - item['dept_tax'], 2)  # 部门税后提成
        item['per_comm'] = round(item['total_comm'] - item['dept_comm'], 2)  # 个人提成
        item['per_tax'] = round(item['tax'] - item['dept_tax'], 2)
        item['per_tax_comm'] = round(item['per_comm'] - item['per_tax'], 2)  # 个人税后提成
    return AllResponse.operate_successfully(data=records)


@customer_api.get('/comm/')  # 查询佣金记录
async def query_customer_commission(ruizy_token: str = Header(...), user: int = Query(None),
                                    start: str = Query(...), end: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    user_id = 'NOUSER' if not user else user
    return query_commission(start, end, user_id=str(user_id))


@customer_api.get('/comm/my/')  # 查询自己客户佣金记录
async def query_customer_commission(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    return query_commission(start, end, user_id=str(person['uid']))


class RiItem(BaseModel):
    create_date: datetime_parse.date
    investor_code: constr(min_length=1)
    cash_in: float
    cash_out: float
    rights: float
    available_funds: float


@customer_api.post('/ri/')  # 添加权益数据rights and interests
async def create_rights(ruizy_token: str = Header(...), ri_list: List[RiItem] = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    for ri in ri_list:
        ri.create_date = ri.create_date.strftime('%Y%m%d')
    ri_list = jsonable_encoder(ri_list)
    sql = """
        INSERT INTO work_customer_rights (create_date,investor_code,rights,cash_out,cash_in,available_funds) 
        VALUES (%(create_date)s,%(investor_code)s,%(rights)s,%(cash_out)s,%(cash_in)s,%(available_funds)s);
    """
    db_conn = FAConnection()
    count, _ = db_conn.insert(sql, param=ri_list, many=True)
    msg = '添加成功，增加数据{}个！'.format(count)
    return AllResponse.operate_successfully(msg=msg)


# 查询权益记录函数
def query_rights(start, end, user_id):
    start_date = datetime_utils.auth_datetime_string(start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(end, f="%Y%m%d")
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='date formate invalid!')
    # user_id = 'NOUSER' if not user else user
    # if user_id == 'NOUSER' and not security.operator_is_super_admin(person):
    #     user_id = person['uid']  # 非管理员只能获取自己客户的数据
    sql = """
        SELECT a.id,a.investor_code,a.create_date,a.rights,a.cash_out,a.cash_in,a.available_funds,b.investor_name 
        FROM work_customer_rights As a 
        INNER JOIN work_customer As b ON a.investor_code=b.investor_code 
        WHERE a.create_date>=%s AND a.create_date<=%s AND IF('NOUSER'=%s,TRUE,b.user_id=%s) 
        ORDER BY a.create_date DESC;
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=[start_date, end_date, user_id, user_id])
    return AllResponse.operate_successfully(data=records)


@customer_api.get('/ri/')  # 查询权益数据
async def query_customer_rights(ruizy_token: str = Header(...), user: int = Query(None),
                                start: str = Query(...), end: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    user_id = 'NOUSER' if not user else str(user)
    return query_rights(start, end, user_id=user_id)


@customer_api.get('/ri/my/')  # 查询我的权益数据
async def query_customer_rights(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    return query_rights(start, end, user_id=str(person['uid']))


@customer_api.get('/ri/statistics/')  # 客户权益统计
async def statistics_customer_rights(ruizy_token: str = Header(...), date: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    query_date = datetime_utils.auth_datetime_string(date, f='%Y%m%d')
    if not query_date:
        return AllResponse.validate_error(msg='date format Error!')
    start_date = (datetime.datetime.strptime(query_date, '%Y%m%d') + datetime.timedelta(days=-360)).strftime('%Y%m%d')
    sql = """
        SELECT a.id,a.investor_code,a.create_date,a.rights,a.cash_out,a.cash_in,a.available_funds,b.investor_name,
            b.user_id,b.admin_name  
        FROM work_customer_rights As a 
        INNER JOIN (
            SELECT b1.investor_name,b1.investor_code,b1.user_id,b2.admin_name  
            FROM work_customer As b1 INNER JOIN ruizy_user As b2 ON b1.user_id=b2.id
        ) 
        As b ON a.investor_code=b.investor_code 
        WHERE a.create_date>=%s AND a.create_date<=%s;
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=[start_date, query_date])
    if len(records) < 1:
        return AllResponse.operate_successfully(data={'statistics_data': [], 'detail_data': []})
    cust_dict = {}
    for cust in records:
        exist = cust_dict.get(cust['investor_code'])
        if exist:
            if exist['create_date'] < cust['create_date']:
                cust_dict[cust['investor_code']] = cust
        else:
            cust_dict[cust['investor_code']] = cust

    cust_list = [v for _, v in cust_dict.items()]
    # 按人统计
    df = pd.DataFrame(cust_list)
    df = df.groupby(by=['user_id', 'admin_name'], as_index=False)[['rights', 'cash_out', 'cash_in', 'available_funds']].sum()
    response_data = {
        'statistics_data': df.to_dict(orient='records'),
        'detail_data': cust_list
    }
    return AllResponse.operate_successfully(data=response_data)